﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;

    public class VanningDetailDA : IVanningDetailDA
    {
        private string AMOUNT = "@AMOUNT";
        private string BAR_CODE = "@BAR_CODE";
        private string CONTAINER_ID = "@CONTAINER_ID";
        private string CONTAINER_NO = "@CONTAINER_NO";
        private string CONTAINER_TYPE = "@CONTAINER_TYPE";
        private string CREATED_BY = "@CREATED_BY";
        private string EXTERN_LINE_ID = "@EXTERN_LINE_ID";
        private string EXTERN_ORDER_ID = "@EXTERN_ORDER_ID";
        private string HEIGHT = "@HEIGHT";
        private string IS_ACTIVE = "@IS_ACTIVE";
        private string LENGTH = "@LENGTH";
        private string LOC = "@LOC";
        private string LOT = "@LOT";
        private string LPN_ID = "@LPN_ID";
        private string MULTI_FLAG = "@MULTI_FLAG";
        private string ORDER_ID = "@ORDER_ID";
        private string ORDER_LINE_ID = "@ORDER_LINE_ID";
        private string OWNER_ID = "@OWNER_ID";
        private string PACK_ID = "@PACK_ID";
        private string PARENT_CONTAINER_ID = "@PARENT_CONTAINER_ID";
        private string PICK_DETAIL_ID = "@PICK_DETAIL_ID";
        private string QTY = "@QTY";
        private string REMARK = "@REMARK";
        private string SEAL_NO = "@SEAL_NO";
        private string SKU_ID = "@SKU_ID";
        private string SQL_CHECK_VANNING_DETAIL_ID_UNIQUE = " SELECT COUNT(1) FROM @_@VANNING_DETAIL WHERE VANNING_ID = @VANNING_ID AND CONTAINER_ID = @CONTAINER_ID ";
        private string SQL_CHECK_VANNING_ID_UNIQUE = " SELECT COUNT(1) FROM @_@VANNING_DETAIL WHERE VANNING_ID = @VANNING_ID AND CONTAINER_ID = @CONTAINER_ID AND ORDER_ID=@ORDER_ID AND ORDER_LINE_ID=@ORDER_LINE_ID AND PICK_DETAIL_ID=@PICK_DETAIL_ID ";
        private string SQL_DELETE_VANNING_DETAIL = " DELETE FROM @_@VANNING_DETAIL WHERE VANNING_ID = @VANNING_ID ";
        private string SQL_DELETE_VANNING_DETAIL_CONTAINER_ID = " DELETE FROM @_@VANNING_DETAIL WHERE VANNING_ID = @VANNING_ID AND CONTAINER_ID = @CONTAINER_ID AND IS_ACTIVE != 'N' ";
        private string SQL_DELETE_VANNING_DETAIL_LINE_ID = " DELETE FROM @_@VANNING_DETAIL WHERE VANNING_ID = @VANNING_ID AND CONTAINER_ID=@CONTAINER_ID AND ORDER_ID=@ORDER_ID AND ORDER_LINE_ID=@ORDER_LINE_ID AND PICK_DETAIL_ID=@PICK_DETAIL_ID ";
        private string SQL_INSERT_VANNING_DETAIL = " INSERT INTO @_@VANNING_DETAIL ( VANNING_ID, VANNIND_LINE_ID, CONTAINER_ID, CONTAINER_TYPE, PARENT_CONTAINER_ID, ORDER_ID, ORDER_LINE_ID, PICK_DETAIL_ID, EXTERN_ORDER_ID, EXTERN_LINE_ID, CONTAINER_NO, SEAL_NO, MULTI_FLAG, STATUS, OWNER_ID, SKU_ID, PACK_ID, UOM_ID, BAR_CODE, LOT, LOC, LPN_ID, TOTAL_QTY, QTY, LENGTH, WIDTH, HEIGHT, VOLUME, WEIGHT, AMOUNT, REMARK, IS_ACTIVE, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE, WH_ID) VALUES ( @VANNING_ID, @VANNIND_LINE_ID, @CONTAINER_ID, @CONTAINER_TYPE, @PARENT_CONTAINER_ID, @ORDER_ID, @ORDER_LINE_ID, @PICK_DETAIL_ID, @EXTERN_ORDER_ID, @EXTERN_LINE_ID, @CONTAINER_NO, @SEAL_NO, @MULTI_FLAG, @STATUS, @OWNER_ID, @SKU_ID, @PACK_ID, @UOM_ID, @BAR_CODE, @LOT, @LOC, @LPN_ID, @TOTAL_QTY, @QTY, @LENGTH, @WIDTH, @HEIGHT, @VOLUME, @WEIGHT, @AMOUNT, @REMARK, @IS_ACTIVE, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE(), @WH_ID )  ";
        private string SQL_SELECT_ALL_VANNING_DETAIL = " SELECT  ROWID, WH_ID, VANNING_ID, VANNIND_LINE_ID, CONTAINER_ID, CONTAINER_TYPE, PARENT_CONTAINER_ID, ORDER_ID, ORDER_LINE_ID, PICK_DETAIL_ID, EXTERN_ORDER_ID, EXTERN_LINE_ID, CONTAINER_NO, SEAL_NO, MULTI_FLAG, STATUS, OWNER_ID, SKU_ID, PACK_ID, UOM_ID, BAR_CODE, LOT, LOC, LPN_ID, (SELECT ISNULL(SUM(QTY),0) FROM @_@VANNING_DETAIL WHERE VANNING_ID=@VANNING_ID AND CONTAINER_ID=@CONTAINER_ID ) AS TOTAL_QTY, QTY, LENGTH, WIDTH, HEIGHT, VOLUME, WEIGHT, AMOUNT, REMARK, IS_ACTIVE, TS, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE  FROM  @_@VANNING_DETAIL  ";
        private string SQL_SELECT_ALL_VANNING_DETAIL_ID_ORDER = " SELECT OD.SKU_ID,OD.ORDER_ID,OD.LINE_ID AS ORDER_LINE_ID,OD.ORIGINAL_QTY AS TOT_QTY,'' AS PICK_DETAIL_ID,'' AS LOT,'' AS LOC,'' AS LPN_ID,VD.VANNED_QTY,VD2.QTY  FROM @_@ORDER_DETAIL OD  LEFT JOIN (SELECT ORDER_ID,ORDER_LINE_ID ,ISNULL(SUM(QTY),0) AS VANNED_QTY  FROM @_@VANNING_DETAIL WHERE ORDER_ID =  @ORDER_ID   GROUP BY ORDER_ID,ORDER_LINE_ID,PICK_DETAIL_ID ) VD ON OD.ORDER_ID = VD.ORDER_ID AND OD.LINE_ID = VD.ORDER_LINE_ID  LEFT JOIN (SELECT CONTAINER_ID,ORDER_ID,ORDER_LINE_ID ,ISNULL(SUM(QTY),0) AS QTY   FROM @_@VANNING_DETAIL WHERE ORDER_ID =  @ORDER_ID AND CONTAINER_ID = @CONTAINER_ID AND VANNING_ID = @VANNING_ID  GROUP BY CONTAINER_ID,ORDER_ID,ORDER_LINE_ID ) VD2 ON OD.ORDER_ID = VD2.ORDER_ID AND OD.LINE_ID = VD2.ORDER_LINE_ID   WHERE OD.ORDER_ID = @ORDER_ID  ";
        private string SQL_SELECT_ALL_VANNING_DETAIL_ID_PICK = " SELECT PD.SKU_ID,PD.PICK_DETAIL_ID,PD.ORDER_ID,PD.ORDER_LINE_ID,PD.QTY AS TOT_QTY,PD.LOT,PD.LOC,PD.LPN_ID,VD.VANNED_QTY,VD2.QTY  FROM @_@PICK_DETAIL PD  LEFT JOIN (SELECT ORDER_ID,ORDER_LINE_ID,PICK_DETAIL_ID,ISNULL(SUM(QTY),0) AS VANNED_QTY   FROM @_@VANNING_DETAIL WHERE ORDER_ID = @ORDER_ID   GROUP BY ORDER_ID,ORDER_LINE_ID,PICK_DETAIL_ID ) VD ON PD.PICK_DETAIL_ID = VD.PICK_DETAIL_ID  LEFT JOIN (SELECT CONTAINER_ID,ORDER_ID,ORDER_LINE_ID,PICK_DETAIL_ID ,ISNULL(SUM(QTY),0) AS QTY   FROM @_@VANNING_DETAIL WHERE ORDER_ID =  @ORDER_ID AND CONTAINER_ID = @CONTAINER_ID AND VANNING_ID = @VANNING_ID  GROUP BY CONTAINER_ID,ORDER_ID,ORDER_LINE_ID,PICK_DETAIL_ID ) VD2 ON PD.PICK_DETAIL_ID = VD2.PICK_DETAIL_ID  WHERE PD.ORDER_ID = @ORDER_ID ";
        private string SQL_SELECT_ALL_VANNING_DETAIL_ORDER = " SELECT  ( CASE (SELECT COUNT(*) FROM @_@VANNING_DETAIL WHERE VANNING_ID=@VANNING_ID AND IS_ACTIVE='Y' AND CONTAINER_ID=VD.CONTAINER_ID) WHEN '0' THEN'空箱' WHEN '1'  THEN (SELECT SKU_ID FROM @_@VANNING_DETAIL  WHERE VANNING_ID=@VANNING_ID AND IS_ACTIVE='Y' AND CONTAINER_ID=VD.CONTAINER_ID) ELSE '混箱' END ) AS SKU_ID,(SELECT ISNULL(SUM(QTY),0) FROM @_@VANNING_DETAIL WHERE VANNING_ID=@VANNING_ID AND IS_ACTIVE='Y' AND CONTAINER_ID=VD.CONTAINER_ID) AS TOTAL_QTY,VANNING_ID, CONTAINER_ID, CONTAINER_TYPE, PARENT_CONTAINER_ID, CONTAINER_NO, SEAL_NO, MULTI_FLAG, LENGTH, WIDTH, HEIGHT, VOLUME, WEIGHT, AMOUNT, REMARK, WH_ID FROM @_@VANNING_DETAIL VD WHERE VANNING_ID=@VANNING_ID  AND IS_ACTIVE ='N' GROUP BY  VANNING_ID, CONTAINER_ID, CONTAINER_TYPE, PARENT_CONTAINER_ID, CONTAINER_NO, SEAL_NO, MULTI_FLAG, LENGTH, WIDTH, HEIGHT, VOLUME, WEIGHT, AMOUNT, REMARK , WH_ID";
        private string SQL_SELECT_ALL_VANNING_DETAIL_WAVE_ORDER = " SELECT OD.SKU_ID,OD.ORDER_ID,OD.LINE_ID AS ORDER_LINE_ID,OD.ORIGINAL_QTY AS TOT_QTY,'' AS PICK_DETAIL_ID ,'' AS LOT,'' AS LOC,'' AS LPN_ID,VD.VANNED_QTY,VD2.QTY  FROM @_@ORDER_DETAIL OD  LEFT JOIN (SELECT ORDER_ID,ORDER_LINE_ID ,ISNULL(SUM(QTY),0) AS VANNED_QTY  FROM @_@VANNING_DETAIL WHERE ORDER_ID IN (SELECT ORDER_ID FROM @_@WAVE_DETAIL WHERE WAVE_ID = @ORDER_ID)  GROUP BY ORDER_ID,ORDER_LINE_ID ) VD ON OD.ORDER_ID = VD.ORDER_ID AND OD.LINE_ID = VD.ORDER_LINE_ID  LEFT JOIN (SELECT CONTAINER_ID,ORDER_ID,ORDER_LINE_ID ,ISNULL(SUM(QTY),0) AS QTY  FROM @_@VANNING_DETAIL WHERE ORDER_ID IN (SELECT ORDER_ID FROM @_@WAVE_DETAIL WHERE WAVE_ID = @ORDER_ID) AND CONTAINER_ID = @CONTAINER_ID AND VANNING_ID = @VANNING_ID  GROUP BY CONTAINER_ID,ORDER_ID,ORDER_LINE_ID ) VD2 ON OD.ORDER_ID = VD2.ORDER_ID AND OD.LINE_ID = VD2.ORDER_LINE_ID  WHERE OD.ORDER_ID IN (SELECT ORDER_ID FROM @_@WAVE_DETAIL WHERE WAVE_ID = @ORDER_ID) ";
        private string SQL_SELECT_ALL_VANNING_DETAIL_WAVE_PICK = " SELECT PD.SKU_ID,PD.PICK_DETAIL_ID,PD.ORDER_ID,PD.ORDER_LINE_ID,PD.QTY AS TOT_QTY,PD.LOT,PD.LOC,PD.LPN_ID,VD.VANNED_QTY,VD2.QTY  FROM @_@PICK_DETAIL PD  LEFT JOIN (SELECT ORDER_ID,ORDER_LINE_ID,PICK_DETAIL_ID,ISNULL(SUM(QTY),0) AS VANNED_QTY   FROM @_@VANNING_DETAIL WHERE ORDER_ID IN (SELECT ORDER_ID FROM @_@WAVE_DETAIL WHERE WAVE_ID = @ORDER_ID)  GROUP BY ORDER_ID,ORDER_LINE_ID,PICK_DETAIL_ID ) VD ON PD.PICK_DETAIL_ID = VD.PICK_DETAIL_ID  LEFT JOIN (SELECT CONTAINER_ID,ORDER_ID,ORDER_LINE_ID,PICK_DETAIL_ID ,ISNULL(SUM(QTY),0) AS QTY  FROM @_@VANNING_DETAIL WHERE ORDER_ID IN (SELECT ORDER_ID FROM @_@WAVE_DETAIL WHERE WAVE_ID = @ORDER_ID)  AND CONTAINER_ID = @CONTAINER_ID AND VANNING_ID = @VANNING_ID  GROUP BY CONTAINER_ID,ORDER_ID,ORDER_LINE_ID,PICK_DETAIL_ID ) VD2 ON PD.PICK_DETAIL_ID = VD2.PICK_DETAIL_ID  WHERE PD.ORDER_ID IN (SELECT ORDER_ID FROM @_@WAVE_DETAIL WHERE WAVE_ID = @ORDER_ID) ";
        private string SQL_SELECT_VANNING_DETAIL_MAX_LINEID = " SELECT ISNULL(MAX(VANNIND_LINE_ID),0) AS VANNIND_LINE_ID FROM @_@VANNING_DETAIL WHERE VANNING_ID = @VANNING_ID ";
        private string SQL_UPDATE_VANNING_DETAIL = " UPDATE @_@VANNING_DETAIL SET CONTAINER_ID = @CONTAINER_ID, CONTAINER_TYPE = @CONTAINER_TYPE, PARENT_CONTAINER_ID = @PARENT_CONTAINER_ID, ORDER_ID = @ORDER_ID, ORDER_LINE_ID = @ORDER_LINE_ID, PICK_DETAIL_ID = @PICK_DETAIL_ID, EXTERN_ORDER_ID = @EXTERN_ORDER_ID, EXTERN_LINE_ID = @EXTERN_LINE_ID, CONTAINER_NO = @CONTAINER_NO, SEAL_NO = @SEAL_NO, MULTI_FLAG = @MULTI_FLAG, STATUS = @STATUS, OWNER_ID = @OWNER_ID, SKU_ID = @SKU_ID, PACK_ID = @PACK_ID, UOM_ID = @UOM_ID, BAR_CODE = @BAR_CODE, LOT = @LOT, LOC = @LOC, LPN_ID = @LPN_ID, TOTAL_QTY = @TOTAL_QTY, QTY = @QTY, LENGTH = @LENGTH, WIDTH = @WIDTH, HEIGHT = @HEIGHT, VOLUME = @VOLUME, WEIGHT = @WEIGHT, AMOUNT = @AMOUNT, REMARK = @REMARK, IS_ACTIVE = @IS_ACTIVE, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE(), WH_ID=@WH_ID WHERE VANNING_ID = @VANNING_ID AND VANNIND_LINE_ID = @VANNIND_LINE_ID  ";
        private string STATUS = "@STATUS";
        private string TOT_QTY = "@TOT_QTY";
        private string TOTAL_QTY = "@TOTAL_QTY";
        private string TS = "@TS";
        private string UOM_ID = "@UOM_ID";
        private string UPDATED_BY = "@UPDATED_BY";
        private string VANNED_QTY = "@VANNED_QTY";
        private string VANNIND_LINE_ID = "@VANNIND_LINE_ID";
        private string VANNING_ID = "@VANNING_ID";
        private string VOLUME = "@VOLUME";
        private string WEIGHT = "@WEIGHT";
        private string WH_ID = "@WH_ID";
        private string WIDTH = "@WIDTH";

        public bool CheckVanningDetailIDUnique(string vanningDetailID, string containerID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@VANNING_ID", vanningDetailID), new SqlParameter("@CONTAINER_ID", containerID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_VANNING_DETAIL_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckVanningDetailIDUnique(DataBase dataBase, DbTransaction tran, string vanningDetailID, string containerID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@VANNING_ID", vanningDetailID), new SqlParameter("@CONTAINER_ID", containerID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_VANNING_DETAIL_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckVanningDetailIDUnique(string vanningDetailID, string containerID, string orderID, string orderLineID, string pickDetailID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@VANNING_ID", vanningDetailID), new SqlParameter("@CONTAINER_ID", containerID), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@ORDER_LINE_ID", orderLineID), new SqlParameter("@PICK_DETAIL_ID", pickDetailID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_VANNING_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckVanningDetailIDUnique(DataBase dataBase, DbTransaction tran, string vanningDetailID, string containerID, string orderID, string orderLineID, string pickDetailID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@VANNING_ID", vanningDetailID), new SqlParameter("@CONTAINER_ID", containerID), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@ORDER_LINE_ID", orderLineID), new SqlParameter("@PICK_DETAIL_ID", pickDetailID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_VANNING_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteVanningDetail(List<string> vanningID, string whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < vanningID.Count; i++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@VANNING_ID", vanningID[i]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_VANNING_DETAIL.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteVanningDetail(DataBase dataBase, DbTransaction tran, List<string> vanningID, string whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < vanningID.Count; i++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@VANNING_ID", vanningID[i]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_VANNING_DETAIL.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteVanningDetail(string vanningID, string containerID, string orderID, string orderLineID, string pickDetailID, string whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@VANNING_ID", vanningID), new SqlParameter("@CONTAINER_ID", containerID), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@ORDER_LINE_ID", orderLineID), new SqlParameter("@PICK_DETAIL_ID", pickDetailID) };
                result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_VANNING_DETAIL_LINE_ID.Replace("@_@", whLoginID), paras);
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteVanningDetail(DataBase dataBase, DbTransaction tran, string vanningID, string containerID, string orderID, string orderLineID, string pickDetailID, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@VANNING_ID", vanningID), new SqlParameter("@CONTAINER_ID", containerID), new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@ORDER_LINE_ID", orderLineID), new SqlParameter("@PICK_DETAIL_ID", pickDetailID) };
                result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_VANNING_DETAIL_LINE_ID.Replace("@_@", whLoginID), paras);
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteVanningDetailByContainerID(string vanningDetailID, string containerID, string whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@VANNING_ID", vanningDetailID), new SqlParameter("@CONTAINER_ID", containerID) };
                result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_VANNING_DETAIL_CONTAINER_ID.Replace("@_@", whLoginID), paras);
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteVanningDetailByContainerID(DataBase dataBase, DbTransaction tran, string vanningDetailID, string containerID, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@VANNING_ID", vanningDetailID), new SqlParameter("@CONTAINER_ID", containerID) };
                result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_VANNING_DETAIL_CONTAINER_ID.Replace("@_@", whLoginID), paras);
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllVanningDetail(string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_VANNING_DETAIL.Replace("@_@", whLoginID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllVanningDetail(DataBase dataBase, DbTransaction tran, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_VANNING_DETAIL.Replace("@_@", whLoginID);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public VanningDetailInfo GetVanningDetailByID(string vanningdetailID, string containerID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_VANNING_DETAIL.Replace("@_@", whLoginID) + " WHERE IS_ACTIVE = 'N' AND CONTAINER_ID = @CONTAINER_ID AND VANNING_ID=@VANNING_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CONTAINER_ID", containerID), new SqlParameter("@VANNING_ID", vanningdetailID) };
            VanningDetailInfo vanningDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    vanningDetailInfo = new VanningDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["VANNING_ID"].ToString(), (reader["VANNIND_LINE_ID"].ToString() != "") ? int.Parse(reader["VANNIND_LINE_ID"].ToString()) : 0, reader["CONTAINER_ID"].ToString(), reader["CONTAINER_TYPE"].ToString(), reader["PARENT_CONTAINER_ID"].ToString(), reader["ORDER_ID"].ToString(), reader["ORDER_LINE_ID"].ToString(), reader["PICK_DETAIL_ID"].ToString(), reader["EXTERN_ORDER_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["CONTAINER_NO"].ToString(), reader["SEAL_NO"].ToString(), reader["MULTI_FLAG"].ToString(), reader["STATUS"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), reader["BAR_CODE"].ToString(), reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), (reader["TOTAL_QTY"].ToString() != "") ? decimal.Parse(reader["TOTAL_QTY"].ToString()) : 0M, (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["LENGTH"].ToString() != "") ? decimal.Parse(reader["LENGTH"].ToString()) : 0M, (reader["WIDTH"].ToString() != "") ? decimal.Parse(reader["WIDTH"].ToString()) : 0M, (reader["HEIGHT"].ToString() != "") ? decimal.Parse(reader["HEIGHT"].ToString()) : 0M, (reader["VOLUME"].ToString() != "") ? decimal.Parse(reader["VOLUME"].ToString()) : 0M, (reader["WEIGHT"].ToString() != "") ? decimal.Parse(reader["WEIGHT"].ToString()) : 0M, (reader["AMOUNT"].ToString() != "") ? decimal.Parse(reader["AMOUNT"].ToString()) : 0M, reader["REMARK"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), 0M, 0M, reader["WH_ID"].ToString());
                }
            }
            return vanningDetailInfo;
        }

        public List<VanningDetailInfo> GetVanningDetailByIDOrder(string vanningdetailID, string orderID, string containerID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_VANNING_DETAIL_ID_ORDER.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@CONTAINER_ID", containerID), new SqlParameter("@VANNING_ID", vanningdetailID) };
            List<VanningDetailInfo> vanningDetailList = new List<VanningDetailInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    VanningDetailInfo vanningDetailInfo = new VanningDetailInfo(reader["SKU_ID"].ToString(), reader["ORDER_ID"].ToString(), reader["ORDER_LINE_ID"].ToString(), reader["PICK_DETAIL_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["TOT_QTY"].ToString() != "") ? decimal.Parse(reader["TOT_QTY"].ToString()) : 0M, (reader["VANNED_QTY"].ToString() != "") ? decimal.Parse(reader["VANNED_QTY"].ToString()) : 0M, reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString());
                    vanningDetailList.Add(vanningDetailInfo);
                }
            }
            return vanningDetailList;
        }

        public List<VanningDetailInfo> GetVanningDetailByIDOrder(DataBase dataBase, DbTransaction tran, string vanningdetailID, string orderID, string containerID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_VANNING_DETAIL_ID_ORDER.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@CONTAINER_ID", containerID), new SqlParameter("@VANNING_ID", vanningdetailID) };
            List<VanningDetailInfo> vanningDetailList = new List<VanningDetailInfo>();
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                VanningDetailInfo vanningDetailInfo = new VanningDetailInfo(reader["SKU_ID"].ToString(), reader["ORDER_ID"].ToString(), reader["ORDER_LINE_ID"].ToString(), reader["PICK_DETAIL_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["TOT_QTY"].ToString() != "") ? decimal.Parse(reader["TOT_QTY"].ToString()) : 0M, (reader["VANNED_QTY"].ToString() != "") ? decimal.Parse(reader["VANNED_QTY"].ToString()) : 0M, reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString());
                vanningDetailList.Add(vanningDetailInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return vanningDetailList;
        }

        public List<VanningDetailInfo> GetVanningDetailByIDPick(string vanningdetailID, string orderID, string containerID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_VANNING_DETAIL_ID_PICK.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@CONTAINER_ID", containerID), new SqlParameter("@VANNING_ID", vanningdetailID) };
            List<VanningDetailInfo> vanningDetailList = new List<VanningDetailInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    VanningDetailInfo vanningDetailInfo = new VanningDetailInfo(reader["SKU_ID"].ToString(), reader["ORDER_ID"].ToString(), reader["ORDER_LINE_ID"].ToString(), reader["PICK_DETAIL_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["TOT_QTY"].ToString() != "") ? decimal.Parse(reader["TOT_QTY"].ToString()) : 0M, (reader["VANNED_QTY"].ToString() != "") ? decimal.Parse(reader["VANNED_QTY"].ToString()) : 0M, reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString());
                    vanningDetailList.Add(vanningDetailInfo);
                }
            }
            return vanningDetailList;
        }

        public List<VanningDetailInfo> GetVanningDetailByIDPick(DataBase dataBase, DbTransaction tran, string vanningdetailID, string orderID, string containerID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_VANNING_DETAIL_ID_PICK.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@CONTAINER_ID", containerID), new SqlParameter("@VANNING_ID", vanningdetailID) };
            List<VanningDetailInfo> vanningDetailList = new List<VanningDetailInfo>();
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                VanningDetailInfo vanningDetailInfo = new VanningDetailInfo(reader["SKU_ID"].ToString(), reader["ORDER_ID"].ToString(), reader["ORDER_LINE_ID"].ToString(), reader["PICK_DETAIL_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["TOT_QTY"].ToString() != "") ? decimal.Parse(reader["TOT_QTY"].ToString()) : 0M, (reader["VANNED_QTY"].ToString() != "") ? decimal.Parse(reader["VANNED_QTY"].ToString()) : 0M, reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString());
                vanningDetailList.Add(vanningDetailInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return vanningDetailList;
        }

        public List<VanningDetailInfo> GetVanningDetailByIDWaveOrder(string vanningdetailID, string orderID, string containerID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_VANNING_DETAIL_WAVE_ORDER.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@CONTAINER_ID", containerID), new SqlParameter("@VANNING_ID", vanningdetailID) };
            List<VanningDetailInfo> vanningDetailList = new List<VanningDetailInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    VanningDetailInfo vanningDetailInfo = new VanningDetailInfo(reader["SKU_ID"].ToString(), reader["ORDER_ID"].ToString(), reader["ORDER_LINE_ID"].ToString(), reader["PICK_DETAIL_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["TOT_QTY"].ToString() != "") ? decimal.Parse(reader["TOT_QTY"].ToString()) : 0M, (reader["VANNED_QTY"].ToString() != "") ? decimal.Parse(reader["VANNED_QTY"].ToString()) : 0M, reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString());
                    vanningDetailList.Add(vanningDetailInfo);
                }
            }
            return vanningDetailList;
        }

        public List<VanningDetailInfo> GetVanningDetailByIDWaveOrder(DataBase dataBase, DbTransaction tran, string vanningdetailID, string orderID, string containerID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_VANNING_DETAIL_WAVE_ORDER.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@CONTAINER_ID", containerID), new SqlParameter("@VANNING_ID", vanningdetailID) };
            List<VanningDetailInfo> vanningDetailList = new List<VanningDetailInfo>();
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                VanningDetailInfo vanningDetailInfo = new VanningDetailInfo(reader["SKU_ID"].ToString(), reader["ORDER_ID"].ToString(), reader["ORDER_LINE_ID"].ToString(), reader["PICK_DETAIL_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["TOT_QTY"].ToString() != "") ? decimal.Parse(reader["TOT_QTY"].ToString()) : 0M, (reader["VANNED_QTY"].ToString() != "") ? decimal.Parse(reader["VANNED_QTY"].ToString()) : 0M, reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString());
                vanningDetailList.Add(vanningDetailInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return vanningDetailList;
        }

        public List<VanningDetailInfo> GetVanningDetailByIDWavePick(string vanningdetailID, string orderID, string containerID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_VANNING_DETAIL_WAVE_PICK.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@CONTAINER_ID", containerID), new SqlParameter("@VANNING_ID", vanningdetailID) };
            List<VanningDetailInfo> vanningDetailList = new List<VanningDetailInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    VanningDetailInfo vanningDetailInfo = new VanningDetailInfo(reader["SKU_ID"].ToString(), reader["ORDER_ID"].ToString(), reader["ORDER_LINE_ID"].ToString(), reader["PICK_DETAIL_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["TOT_QTY"].ToString() != "") ? decimal.Parse(reader["TOT_QTY"].ToString()) : 0M, (reader["VANNED_QTY"].ToString() != "") ? decimal.Parse(reader["VANNED_QTY"].ToString()) : 0M, reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString());
                    vanningDetailList.Add(vanningDetailInfo);
                }
            }
            return vanningDetailList;
        }

        public List<VanningDetailInfo> GetVanningDetailByIDWavePick(DataBase dataBase, DbTransaction tran, string vanningdetailID, string orderID, string containerID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_VANNING_DETAIL_WAVE_PICK.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@CONTAINER_ID", containerID), new SqlParameter("@VANNING_ID", vanningdetailID) };
            List<VanningDetailInfo> vanningDetailList = new List<VanningDetailInfo>();
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                VanningDetailInfo vanningDetailInfo = new VanningDetailInfo(reader["SKU_ID"].ToString(), reader["ORDER_ID"].ToString(), reader["ORDER_LINE_ID"].ToString(), reader["PICK_DETAIL_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["TOT_QTY"].ToString() != "") ? decimal.Parse(reader["TOT_QTY"].ToString()) : 0M, (reader["VANNED_QTY"].ToString() != "") ? decimal.Parse(reader["VANNED_QTY"].ToString()) : 0M, reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString());
                vanningDetailList.Add(vanningDetailInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return vanningDetailList;
        }

        public List<VanningDetailInfo> GetVanningDetailByOrder(string vanningdetailID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_VANNING_DETAIL_ORDER.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@VANNING_ID", vanningdetailID) };
            List<VanningDetailInfo> vanningDetailList = new List<VanningDetailInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002 = new DateTime();
                    CS00002 = new DateTime();
                    VanningDetailInfo vanningDetailInfo = new VanningDetailInfo(0, reader["VANNING_ID"].ToString(), 0, reader["CONTAINER_ID"].ToString(), reader["CONTAINER_TYPE"].ToString(), reader["PARENT_CONTAINER_ID"].ToString(), "", "", "", "", "", reader["CONTAINER_NO"].ToString(), reader["SEAL_NO"].ToString(), reader["MULTI_FLAG"].ToString(), "", "", reader["SKU_ID"].ToString(), "", "", "", "", "", "", (reader["TOTAL_QTY"].ToString() != "") ? decimal.Parse(reader["TOTAL_QTY"].ToString()) : 0M, 0M, (reader["LENGTH"].ToString() != "") ? decimal.Parse(reader["LENGTH"].ToString()) : 0M, (reader["WIDTH"].ToString() != "") ? decimal.Parse(reader["WIDTH"].ToString()) : 0M, (reader["HEIGHT"].ToString() != "") ? decimal.Parse(reader["HEIGHT"].ToString()) : 0M, (reader["VOLUME"].ToString() != "") ? decimal.Parse(reader["VOLUME"].ToString()) : 0M, (reader["WEIGHT"].ToString() != "") ? decimal.Parse(reader["WEIGHT"].ToString()) : 0M, (reader["AMOUNT"].ToString() != "") ? decimal.Parse(reader["AMOUNT"].ToString()) : 0M, reader["REMARK"].ToString(), "", new byte[0], "", CS00002, "", CS00002, 0M, 0M, reader["WH_ID"].ToString());
                    vanningDetailList.Add(vanningDetailInfo);
                }
            }
            return vanningDetailList;
        }

        public int GetVanningDetailMaxLineID(string vanningID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@VANNING_ID", vanningID) };
            return int.Parse(DBHelper.ExecuteScalar(CommandType.Text, this.SQL_SELECT_VANNING_DETAIL_MAX_LINEID.Replace("@_@", whLoginID), paras).ToString());
        }

        public int GetVanningDetailMaxLineID(DataBase dataBase, DbTransaction tran, string vanningID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@VANNING_ID", vanningID) };
            return int.Parse(dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_SELECT_VANNING_DETAIL_MAX_LINEID.Replace("@_@", whLoginID), paras).ToString());
        }

        public int InsertVanningDetail(VanningDetailInfo vanningDetailInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                vanningDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_VanningDetail_Parameters(vanningDetailInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_VANNING_DETAIL.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertVanningDetail(DataBase dataBase, DbTransaction tran, VanningDetailInfo vanningDetailInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                vanningDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_VanningDetail_Parameters(vanningDetailInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_VANNING_DETAIL.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_VanningDetail_Parameters(VanningDetailInfo vanningDetailInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.VANNING_ID, SqlDbType.VarChar, 50), new SqlParameter(this.VANNIND_LINE_ID, SqlDbType.Int, 4), new SqlParameter(this.CONTAINER_ID, SqlDbType.VarChar, 50), new SqlParameter(this.CONTAINER_TYPE, SqlDbType.VarChar, 50), new SqlParameter(this.PARENT_CONTAINER_ID, SqlDbType.VarChar, 50), new SqlParameter(this.ORDER_ID, SqlDbType.VarChar, 50), new SqlParameter(this.ORDER_LINE_ID, SqlDbType.VarChar, 50), new SqlParameter(this.PICK_DETAIL_ID, SqlDbType.VarChar, 50), new SqlParameter(this.EXTERN_ORDER_ID, SqlDbType.VarChar, 50), new SqlParameter(this.EXTERN_LINE_ID, SqlDbType.VarChar, 50), new SqlParameter(this.CONTAINER_NO, SqlDbType.VarChar, 50), new SqlParameter(this.SEAL_NO, SqlDbType.VarChar, 50), new SqlParameter(this.MULTI_FLAG, SqlDbType.VarChar, 1), new SqlParameter(this.STATUS, SqlDbType.VarChar, 50), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 50), new SqlParameter(this.SKU_ID, SqlDbType.VarChar, 50), 
                new SqlParameter(this.PACK_ID, SqlDbType.VarChar, 50), new SqlParameter(this.UOM_ID, SqlDbType.VarChar, 50), new SqlParameter(this.BAR_CODE, SqlDbType.VarChar, 50), new SqlParameter(this.LOT, SqlDbType.VarChar, 50), new SqlParameter(this.LOC, SqlDbType.VarChar, 50), new SqlParameter(this.LPN_ID, SqlDbType.VarChar, 50), new SqlParameter(this.TOTAL_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.QTY, SqlDbType.Decimal, 13), new SqlParameter(this.LENGTH, SqlDbType.Decimal, 13), new SqlParameter(this.WIDTH, SqlDbType.Decimal, 13), new SqlParameter(this.HEIGHT, SqlDbType.Decimal, 13), new SqlParameter(this.VOLUME, SqlDbType.Decimal, 13), new SqlParameter(this.WEIGHT, SqlDbType.Decimal, 13), new SqlParameter(this.AMOUNT, SqlDbType.Decimal, 13), new SqlParameter(this.REMARK, SqlDbType.VarChar, 300), new SqlParameter(this.IS_ACTIVE, SqlDbType.VarChar, 1), 
                new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 50), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 50), new SqlParameter(this.TOT_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.VANNED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30)
             };
            paramArray[0].Value = vanningDetailInfo.VanningID;
            paramArray[1].Value = vanningDetailInfo.VannindLineID;
            paramArray[2].Value = vanningDetailInfo.ContainerID;
            paramArray[3].Value = vanningDetailInfo.ContainerType;
            if (!string.IsNullOrEmpty(vanningDetailInfo.ParentContainerID))
            {
                paramArray[4].Value = vanningDetailInfo.ParentContainerID;
            }
            else
            {
                paramArray[4].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vanningDetailInfo.OrderID))
            {
                paramArray[5].Value = vanningDetailInfo.OrderID;
            }
            else
            {
                paramArray[5].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vanningDetailInfo.OrderLineID))
            {
                paramArray[6].Value = vanningDetailInfo.OrderLineID;
            }
            else
            {
                paramArray[6].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vanningDetailInfo.PickDetailID))
            {
                paramArray[7].Value = vanningDetailInfo.PickDetailID;
            }
            else
            {
                paramArray[7].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vanningDetailInfo.ExternOrderID))
            {
                paramArray[8].Value = vanningDetailInfo.ExternOrderID;
            }
            else
            {
                paramArray[8].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vanningDetailInfo.ExternLineID))
            {
                paramArray[9].Value = vanningDetailInfo.ExternLineID;
            }
            else
            {
                paramArray[9].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vanningDetailInfo.ContainerNo))
            {
                paramArray[10].Value = vanningDetailInfo.ContainerNo;
            }
            else
            {
                paramArray[10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vanningDetailInfo.SealNo))
            {
                paramArray[11].Value = vanningDetailInfo.SealNo;
            }
            else
            {
                paramArray[11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vanningDetailInfo.MultiFlag))
            {
                paramArray[12].Value = vanningDetailInfo.MultiFlag;
            }
            else
            {
                paramArray[12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vanningDetailInfo.Status))
            {
                paramArray[13].Value = vanningDetailInfo.Status;
            }
            else
            {
                paramArray[13].Value = DBNull.Value;
            }
            paramArray[14].Value = vanningDetailInfo.OwnerID;
            paramArray[15].Value = vanningDetailInfo.SkuID;
            if (!string.IsNullOrEmpty(vanningDetailInfo.PackID))
            {
                paramArray[0x10].Value = vanningDetailInfo.PackID;
            }
            else
            {
                paramArray[0x10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vanningDetailInfo.UomID))
            {
                paramArray[0x11].Value = vanningDetailInfo.UomID;
            }
            else
            {
                paramArray[0x11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vanningDetailInfo.BarCode))
            {
                paramArray[0x12].Value = vanningDetailInfo.BarCode;
            }
            else
            {
                paramArray[0x12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vanningDetailInfo.Lot))
            {
                paramArray[0x13].Value = vanningDetailInfo.Lot;
            }
            else
            {
                paramArray[0x13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vanningDetailInfo.Loc))
            {
                paramArray[20].Value = vanningDetailInfo.Loc;
            }
            else
            {
                paramArray[20].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vanningDetailInfo.LpnID))
            {
                paramArray[0x15].Value = vanningDetailInfo.LpnID;
            }
            else
            {
                paramArray[0x15].Value = DBNull.Value;
            }
            paramArray[0x16].Value = vanningDetailInfo.TotalQty;
            paramArray[0x17].Value = vanningDetailInfo.Qty;
            paramArray[0x18].Value = vanningDetailInfo.Length;
            paramArray[0x19].Value = vanningDetailInfo.Width;
            paramArray[0x1a].Value = vanningDetailInfo.Height;
            paramArray[0x1b].Value = vanningDetailInfo.Volume;
            paramArray[0x1c].Value = vanningDetailInfo.Weight;
            paramArray[0x1d].Value = vanningDetailInfo.Amount;
            if (!string.IsNullOrEmpty(vanningDetailInfo.Remark))
            {
                paramArray[30].Value = vanningDetailInfo.Remark;
            }
            else
            {
                paramArray[30].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(vanningDetailInfo.IsActive))
            {
                paramArray[0x1f].Value = vanningDetailInfo.IsActive;
            }
            else
            {
                paramArray[0x1f].Value = DBNull.Value;
            }
            paramArray[0x20].Value = vanningDetailInfo.CreatedBy;
            paramArray[0x21].Value = vanningDetailInfo.UpdatedBy;
            paramArray[0x22].Value = vanningDetailInfo.TotQty;
            paramArray[0x23].Value = vanningDetailInfo.VannedQty;
            if (!string.IsNullOrEmpty(vanningDetailInfo.WhID))
            {
                paramArray[0x24].Value = vanningDetailInfo.WhID;
                return paramArray;
            }
            paramArray[0x24].Value = DBNull.Value;
            return paramArray;
        }

        public int UpdateVanningDetail(VanningDetailInfo vanningDetailInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                vanningDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_VanningDetail_Parameters(vanningDetailInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_VANNING_DETAIL.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateVanningDetail(DataBase dataBase, DbTransaction tran, VanningDetailInfo vanningDetailInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                vanningDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_VanningDetail_Parameters(vanningDetailInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_VANNING_DETAIL.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

